const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 获取课表数据
router.get('/', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { classId, week, semester } = req.query;
    
    if (!classId) {
      return res.status(400).json({ message: '缺少班级ID参数' });
    }
    
    // 修改查询语句，添加semester参数
    const [timetable] = await pool.query(`
      SELECT 
        t.id,
        t.classId,
        t.courseId,
        c.name as courseName,
        t.teacherId,
        u.name as teacherName,
        t.roomId,
        cr.roomNumber as roomName,
        t.day,
        t.period,
        t.startWeek,
        t.endWeek,
        t.semester
      FROM timetables t
      JOIN courses c ON t.courseId = c.id
      JOIN users u ON t.teacherId = u.id
      JOIN classrooms cr ON t.roomId = cr.id
      WHERE t.classId = ? 
        AND ? BETWEEN t.startWeek AND t.endWeek
        ${semester ? 'AND t.semester = ?' : ''}
    `, semester ? [classId, week || 1, semester] : [classId, week || 1]);
    
    // 转换为前端需要的格式
    const formattedTimetable = timetable.map(course => ({
      id: course.id,
      day: course.day,
      period: course.period,
      name: course.courseName,
      teacher: course.teacherName,
      location: course.roomName,
      startWeek: course.startWeek,
      endWeek: course.endWeek,
      semester: course.semester
    }));
    
    res.json({ timetable: formattedTimetable });
  } catch (error) {
    console.error('获取课表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 添加课程
router.post('/', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { classId, courseId, teacherId, roomId, day, period, startWeek, endWeek, semester } = req.body;
    
    // 验证必要参数
    if (!classId || !courseId || !teacherId || !roomId || day === undefined || period === undefined || !startWeek || !endWeek || !semester) {
      return res.status(400).json({ message: '缺少必要参数' });
    }
    
    // 检查是否有冲突
    const [conflicts] = await pool.query(`
      SELECT * FROM timetables
      WHERE roomId = ? AND day = ? AND period = ?
        AND ((startWeek <= ? AND endWeek >= ?) OR (startWeek <= ? AND endWeek >= ?))
        AND semester = ?
    `, [roomId, day, period, startWeek, startWeek, endWeek, endWeek, semester]);
    
    if (conflicts.length > 0) {
      return res.status(400).json({ message: '该时间段教室已被占用' });
    }
    
    // 添加课程，直接使用前端传递的 semester 值
    await pool.query(`
      INSERT INTO timetables (classId, courseId, teacherId, roomId, day, period, startWeek, endWeek, semester)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [classId, courseId, teacherId, roomId, day, period, startWeek, endWeek, semester]);
    
    res.json({ message: '添加成功' });
  } catch (error) {
    console.error('添加课程失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 导出课表
router.get('/export', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { classId, week } = req.query;
    
    if (!classId) {
      return res.status(400).json({ message: '缺少班级ID参数' });
    }
    
    // 获取班级信息
    const [classInfo] = await pool.query('SELECT name FROM classes WHERE id = ?', [classId]);
    
    if (classInfo.length === 0) {
      return res.status(404).json({ message: '班级不存在' });
    }
    
    // 获取课表数据
    const [timetable] = await pool.query(`
      SELECT 
        t.id,
        c.name as courseName,
        u.name as teacherName,
        cr.roomNumber as roomName,
        t.day,
        t.period,
        t.startWeek,
        t.endWeek
      FROM timetables t
      JOIN courses c ON t.courseId = c.id
      JOIN users u ON t.teacherId = u.id
      JOIN classrooms cr ON t.roomId = cr.id
      WHERE t.classId = ? 
        AND ? BETWEEN t.startWeek AND t.endWeek
    `, [classId, week || 1]);
    
    // 创建Excel文件
    const Excel = require('exceljs');
    const path = require('path');
    const fs = require('fs');
    
    // 生成文件名
    const fileName = `${classInfo[0].name}_第${week || 1}周课表_${new Date().toISOString().slice(0, 10)}.xlsx`;
    const filePath = path.join(__dirname, '../public/exports', fileName);
    
    // 确保目录存在
    if (!fs.existsSync(path.join(__dirname, '../public/exports'))) {
      fs.mkdirSync(path.join(__dirname, '../public/exports'), { recursive: true });
    }
    
    // 创建工作簿和工作表
    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet('课表');
    
    // 设置列
    worksheet.columns = [
      { header: '星期', key: 'day', width: 10 },
      { header: '节次', key: 'period', width: 10 },
      { header: '课程', key: 'courseName', width: 20 },
      { header: '教师', key: 'teacherName', width: 15 },
      { header: '教室', key: 'roomName', width: 15 },
      { header: '起始周', key: 'startWeek', width: 10 },
      { header: '结束周', key: 'endWeek', width: 10 }
    ];
    
    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    
    // 添加数据
    const weekdays = ['周一', '周二', '周三', '周四', '周五', '周六', '周日'];
    const periods = ['第1节', '第2节', '第3节', '第4节', '第5节', '第6节', '第7节', '第8节', '第9节', '第10节'];
    
    timetable.forEach(course => {
      worksheet.addRow({
        day: weekdays[course.day],
        period: periods[course.period],
        courseName: course.courseName,
        teacherName: course.teacherName,
        roomName: course.roomName,
        startWeek: course.startWeek,
        endWeek: course.endWeek
      });
    });
    
    // 保存文件
    await workbook.xlsx.writeFile(filePath);
    
    // 设置响应头，直接下载文件
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename=${encodeURIComponent(fileName)}`);
    
    // 发送文件
    fs.createReadStream(filePath).pipe(res);
  } catch (error) {
    console.error('导出课表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取教室列表
router.get('/rooms', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    // 查询所有可用的教室
    const [rooms] = await pool.query(`
      SELECT 
        c.id,
        c.roomNumber,
        b.name as buildingName,
        b.code as buildingCode,
        c.capacity,
        c.type,
        c.floor,
        c.status
      FROM classrooms c
      JOIN buildings b ON c.buildingId = b.id
      WHERE c.status != 'maintenance'
      ORDER BY b.name, c.floor, c.roomNumber
    `);
    
    // 格式化教室数据
    const formattedRooms = rooms.map(room => ({
      id: room.id,
      name: `${room.buildingName} ${room.floor}楼-${room.roomNumber}`,
      buildingCode: room.buildingCode,
      roomNumber: room.roomNumber,
      capacity: room.capacity,
      type: room.type,
      status: room.status
    }));
    
    res.json(formattedRooms);
  } catch (error) {
    console.error('获取教室列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取教师列表
router.get('/teachers', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    // 修改查询语句，移除不存在的 username 字段
    const [teachers] = await pool.query(`
      SELECT id, name, email, phone
      FROM users
      WHERE role = 'teacher'
      ORDER BY name
    `);
    
    res.json(teachers);
  } catch (error) {
    console.error('获取教师列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取课程列表
router.get('/courses', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    

    const [courses] = await pool.query(`
      SELECT id, name, credits
      FROM courses
      ORDER BY name
    `);
    
    res.json(courses);
  } catch (error) {
    console.error('获取课程列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取班级信息
router.get('/classes/:id', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const classId = req.params.id;
    
    // 查询班级信息
    const [classInfo] = await pool.query(`
      SELECT id, className as name, department, grade, counselorId, studentCount
      FROM classes
      WHERE id = ?
    `, [classId]);
    
    if (classInfo.length === 0) {
      return res.status(404).json({ message: '班级不存在' });
    }
    
    res.json(classInfo[0]);
  } catch (error) {
    console.error('获取班级信息失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取学期列表
router.get('/semesters', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    // 获取当前年份
    const currentYear = new Date().getFullYear();
    const currentMonth = new Date().getMonth() + 1; // 月份从0开始，所以+1
    
    // 确定当前学期
    let currentSemester;
    if (currentMonth >= 2 && currentMonth <= 7) {
      // 2-7月为第二学期
      currentSemester = `${currentYear-1}-${currentYear}-2`;
    } else {
      // 8-1月为第一学期
      currentSemester = `${currentYear}-${currentYear+1}-1`;
    }
    
    // 从多个表中获取学期信息
    const [timetableSemesters] = await pool.query(`
      SELECT DISTINCT semester as id, semester as name
      FROM timetables
      WHERE semester IS NOT NULL
    `);
    
    const [courseSemesters] = await pool.query(`
      SELECT DISTINCT semester as id, semester as name
      FROM courses
      WHERE semester IS NOT NULL
    `);
    
    // 合并学期数据并去重
    let allSemesters = [...timetableSemesters, ...courseSemesters];
    let uniqueSemesters = [];
    let semesterIds = new Set();
    
    allSemesters.forEach(semester => {
      if (semester.id && !semesterIds.has(semester.id)) {
        semesterIds.add(semester.id);
        uniqueSemesters.push(semester);
      }
    });
    
    // 如果数据库中没有学期数据，或者数据太少，添加默认学期
    if (uniqueSemesters.length < 4) {
      // 添加当前学年的两个学期
      if (!semesterIds.has(`${currentYear}-${currentYear+1}-1`)) {
        uniqueSemesters.push({ 
          id: `${currentYear}-${currentYear+1}-1`, 
          name: `${currentYear}-${currentYear+1}-1` 
        });
      }
      
      if (!semesterIds.has(`${currentYear}-${currentYear+1}-2`)) {
        uniqueSemesters.push({ 
          id: `${currentYear}-${currentYear+1}-2`, 
          name: `${currentYear}-${currentYear+1}-2` 
        });
      }
      
      // 添加上一学年的两个学期
      if (!semesterIds.has(`${currentYear-1}-${currentYear}-1`)) {
        uniqueSemesters.push({ 
          id: `${currentYear-1}-${currentYear}-1`, 
          name: `${currentYear-1}-${currentYear}-1` 
        });
      }
      
      if (!semesterIds.has(`${currentYear-1}-${currentYear}-2`)) {
        uniqueSemesters.push({ 
          id: `${currentYear-1}-${currentYear}-2`, 
          name: `${currentYear-1}-${currentYear}-2` 
        });
      }
    }
    
    // 按学期排序（降序）
    uniqueSemesters.sort((a, b) => {
      return b.id.localeCompare(a.id);
    });
    
    // 为每个学期添加maxWeeks属性和isCurrent标记
    const semestersWithWeeks = uniqueSemesters.map(semester => ({
      ...semester,
      maxWeeks: 20, // 默认值，可以从配置或其他表中获取
      isCurrent: semester.id === currentSemester
    }));
    
    // 返回学期列表
    res.json(semestersWithWeeks);
  } catch (error) {
    console.error('获取学期列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取当前周次和学期
router.get('/current', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    // 获取当前日期
    const currentDate = new Date();
    const currentYear = currentDate.getFullYear();
    const currentMonth = currentDate.getMonth() + 1; // 月份从0开始，所以+1
    
    // 确定当前学期
    let currentSemester;
    if (currentMonth >= 2 && currentMonth <= 7) {
      // 2-7月为第二学期
      currentSemester = `${currentYear-1}-${currentYear}-2`;
    } else {
      // 8-1月为第一学期
      currentSemester = `${currentYear}-${currentYear+1}-1`;
    }
    
    // 计算当前周次
    let currentWeek = 1;
    const now = new Date();
    
    if (currentMonth >= 2 && currentMonth <= 7) {
      // 第二学期从2月开始
      const semesterStart = new Date(currentYear, 1, 1); // 2月1日
      const diffTime = Math.abs(now - semesterStart);
      const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
      currentWeek = Math.ceil(diffDays / 7);
    } else if (currentMonth >= 8) {
      // 第一学期从9月开始
      const semesterStart = new Date(currentYear, 8, 1); // 9月1日
      const diffTime = Math.abs(now - semesterStart);
      const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
      currentWeek = Math.ceil(diffDays / 7);
    } else {
      // 1月属于上一年的第一学期
      const semesterStart = new Date(currentYear - 1, 8, 1); // 上一年9月1日
      const diffTime = Math.abs(now - semesterStart);
      const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
      currentWeek = Math.ceil(diffDays / 7);
    }
    
    // 确保周次在合理范围内
    const maxWeeks = 20;
    currentWeek = Math.min(Math.max(currentWeek, 1), maxWeeks);
    
    // 尝试从数据库获取学期信息
    try {
      const [semesters] = await pool.query(`
        SELECT DISTINCT semester as id
        FROM timetables
        WHERE semester IS NOT NULL
        ORDER BY semester DESC
      `);
      
      // 如果数据库中有学期数据，检查当前计算的学期是否存在
      if (semesters && semesters.length > 0) {
        const semesterExists = semesters.some(s => s.id === currentSemester);
        
        // 如果计算的当前学期不存在于数据库中，使用数据库中最新的学期
        if (!semesterExists) {
          currentSemester = semesters[0].id;
        }
      }
    } catch (error) {
      console.log('获取学期列表失败，使用计算的默认学期:', error.message);
    }
    
    res.json({
      currentWeek,
      currentSemester,
      maxWeeks
    });
  } catch (error) {
    console.error('获取当前周次和学期失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 删除课程
router.delete('/:id', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const courseId = req.params.id;
    
    // 删除课程
    await pool.query('DELETE FROM timetables WHERE id = ?', [courseId]);
    
    res.json({ message: '删除成功' });
  } catch (error) {
    console.error('删除课程失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 导入课表
router.post('/import', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { classId, semester, timetableData } = req.body;
    
    if (!classId || !semester || !timetableData || !Array.isArray(timetableData)) {
      return res.status(400).json({ message: '参数错误' });
    }
    
    // 开始事务
    const connection = await pool.getConnection();
    await connection.beginTransaction();
    
    try {
      // 清除该班级在指定学期的所有课表数据
      await connection.query(
        'DELETE FROM timetables WHERE classId = ? AND semester = ?',
        [classId, semester]
      );
      
      // 批量插入新数据
      if (timetableData.length > 0) {
        const values = timetableData.map(item => [
          classId,
          item.courseId,
          item.teacherId,
          item.roomId,
          item.day,
          item.period,
          item.startWeek,
          item.endWeek,
          semester
        ]);
        
        await connection.query(`
          INSERT INTO timetables 
          (classId, courseId, teacherId, roomId, day, period, startWeek, endWeek, semester)
          VALUES ?
        `, [values]);
      }
      
      // 提交事务
      await connection.commit();
      res.json({ message: '导入成功' });
    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }
  } catch (error) {
    console.error('导入课表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取班级课表模板
router.get('/template/:classId', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const classId = req.params.classId;
    
    // 获取班级信息
    const [classInfo] = await pool.query('SELECT className FROM classes WHERE id = ?', [classId]);
    
    if (classInfo.length === 0) {
      return res.status(404).json({ message: '班级不存在' });
    }
    
    // 创建Excel模板
    const Excel = require('exceljs');
    const path = require('path');
    const fs = require('fs');
    
    // 生成文件名
    const fileName = `${classInfo[0].className}_课表模板_${new Date().toISOString().slice(0, 10)}.xlsx`;
    const filePath = path.join(__dirname, '../public/exports', fileName);
    
    // 确保目录存在
    if (!fs.existsSync(path.join(__dirname, '../public/exports'))) {
      fs.mkdirSync(path.join(__dirname, '../public/exports'), { recursive: true });
    }
    
    // 创建工作簿和工作表
    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet('课表模板');
    
    // 设置列
    worksheet.columns = [
      { header: '课程ID', key: 'courseId', width: 10 },
      { header: '课程名称', key: 'courseName', width: 20 },
      { header: '教师ID', key: 'teacherId', width: 10 },
      { header: '教师姓名', key: 'teacherName', width: 15 },
      { header: '教室ID', key: 'roomId', width: 10 },
      { header: '教室名称', key: 'roomName', width: 15 },
      { header: '星期(0-6)', key: 'day', width: 10 },
      { header: '节次(0-9)', key: 'period', width: 10 },
      { header: '起始周', key: 'startWeek', width: 10 },
      { header: '结束周', key: 'endWeek', width: 10 }
    ];
    
    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    
    // 添加说明行
    worksheet.addRow({
      courseId: '必填',
      courseName: '参考',
      teacherId: '必填',
      teacherName: '参考',
      roomId: '必填',
      roomName: '参考',
      day: '0-6(周一到周日)',
      period: '0-9(第1到第10节)',
      startWeek: '1-20',
      endWeek: '1-20'
    });
    
    // 获取课程列表
    const [courses] = await pool.query('SELECT id, name FROM courses ORDER BY name');
    
    // 获取教师列表
    const [teachers] = await pool.query('SELECT id, name FROM users WHERE role = "teacher" ORDER BY name');
    
    // 获取教室列表
    const [rooms] = await pool.query(`
      SELECT c.id, CONCAT(b.name, ' ', c.floor, '楼-', c.roomNumber) as name
      FROM classrooms c
      JOIN buildings b ON c.buildingId = b.id
      WHERE c.status != 'maintenance'
      ORDER BY b.name, c.floor, c.roomNumber
    `);
    
    // 添加参考数据
    const refSheet = workbook.addWorksheet('参考数据');
    
    refSheet.columns = [
      { header: '课程ID', key: 'courseId', width: 10 },
      { header: '课程名称', key: 'courseName', width: 20 },
      { header: '教师ID', key: 'teacherId', width: 10 },
      { header: '教师姓名', key: 'teacherName', width: 15 },
      { header: '教室ID', key: 'roomId', width: 10 },
      { header: '教室名称', key: 'roomName', width: 20 }
    ];
    
    refSheet.getRow(1).font = { bold: true };
    
    // 添加课程数据
    courses.forEach(course => {
      refSheet.addRow({
        courseId: course.id,
        courseName: course.name
      });
    });
    
    // 添加空行
    refSheet.addRow({});
    
    // 添加教师数据
    teachers.forEach(teacher => {
      refSheet.addRow({
        teacherId: teacher.id,
        teacherName: teacher.name
      });
    });
    
    // 添加空行
    refSheet.addRow({});
    
    // 添加教室数据
    rooms.forEach(room => {
      refSheet.addRow({
        roomId: room.id,
        roomName: room.name
      });
    });
    
    // 保存文件
    await workbook.xlsx.writeFile(filePath);
    
    // 设置响应头，直接下载文件
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename=${encodeURIComponent(fileName)}`);
    
    // 发送文件
    fs.createReadStream(filePath).pipe(res);
  } catch (error) {
    console.error('生成课表模板失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取单个课程详情
router.get('/detail/:id', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const timetableId = req.params.id;
    
    // 查询单个课程详情
    const [timetable] = await pool.query(`
      SELECT 
        t.id,
        t.classId,
        t.courseId,
        c.name as courseName,
        t.teacherId,
        u.name as teacherName,
        t.roomId,
        cr.roomNumber as roomName,
        t.day,
        t.period,
        t.startWeek,
        t.endWeek,
        t.semester
      FROM timetables t
      JOIN courses c ON t.courseId = c.id
      JOIN users u ON t.teacherId = u.id
      JOIN classrooms cr ON t.roomId = cr.id
      WHERE t.id = ?
    `, [timetableId]);
    
    if (timetable.length === 0) {
      return res.status(404).json({ message: '未找到课程信息' });
    }
    
    // 返回课程详情
    res.json(timetable[0]);
  } catch (error) {
    console.error('获取课程详情失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 复制课表 - 从一个班级复制到另一个班级
router.post('/copy', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { sourceClassId, targetClassId, startWeek, endWeek } = req.body;
    
    // 参数验证
    if (!sourceClassId || !targetClassId) {
      return res.status(400).json({ message: '缺少必要参数' });
    }
    
    if (sourceClassId === targetClassId) {
      return res.status(400).json({ message: '源班级和目标班级不能相同' });
    }
    
    // 验证周次范围
    if (!startWeek || !endWeek || startWeek > endWeek || startWeek < 1 || endWeek > 20) {
      return res.status(400).json({ message: '周次范围无效' });
    }
    
    // 1. 获取源班级的课表数据
    const [sourceTimetables] = await pool.query(
      `SELECT * FROM timetables WHERE classId = ?`,
      [sourceClassId]
    );
    
    if (sourceTimetables.length === 0) {
      return res.status(404).json({ message: '源班级没有课表数据' });
    }
    
    // 2. 删除目标班级在指定周次范围内的现有课表数据
    await pool.query(
      `DELETE FROM timetables 
       WHERE classId = ? 
       AND ((startWeek BETWEEN ? AND ?) 
       OR (endWeek BETWEEN ? AND ?) 
       OR (startWeek <= ? AND endWeek >= ?))`,
      [targetClassId, startWeek, endWeek, startWeek, endWeek, startWeek, endWeek]
    );
    
    // 3. 复制源班级的课表数据到目标班级
    let insertCount = 0;
    for (const timetable of sourceTimetables) {
      // 调整周次范围，确保在指定范围内
      const newStartWeek = Math.max(timetable.startWeek, startWeek);
      const newEndWeek = Math.min(timetable.endWeek, endWeek);
      
      // 只复制周次范围有重叠的课程
      if (newStartWeek <= newEndWeek) {
        await pool.query(
          `INSERT INTO timetables 
           (classId, courseId, teacherId, roomId, day, period, startWeek, endWeek, semester) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            targetClassId,
            timetable.courseId,
            timetable.teacherId,
            timetable.roomId,
            timetable.day,
            timetable.period,
            newStartWeek,
            newEndWeek,
            timetable.semester
          ]
        );
        insertCount++;
      }
    }
    
    res.status(200).json({ 
      message: '课表复制成功', 
      count: insertCount 
    });
    
  } catch (error) {
    console.error('复制课表错误:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

module.exports = router;